package com.dean.utils;


import cn.hutool.core.util.StrUtil;
import com.dean.constants.DeanUiConstants;
import com.dean.entity.*;
import com.dean.exception.DeanRuntimeException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;

/**
 * @author dean
 * @Description 数据库操作工具
 * @create 2020-12-12 11:46
 */
public class DeanJdbcUtils<T> {

	private static final Logger logger = LoggerFactory.getLogger(DeanJdbcUtils.class);

	private static SysDbConnInfo defaultConnInfo = null;

	private static final String JDBC_CFG_PATH = DeanUiConstants.INIT_PATH+"jdbc.properties";

	static{
		Properties jdbc = PropertyUtils.readProperties(JDBC_CFG_PATH);
		defaultConnInfo = new SysDbConnInfo();
		defaultConnInfo.setDriver(jdbc.getProperty("driver"));
		defaultConnInfo.setUrl(jdbc.getProperty("url"));
	}

	/**
	 * 获取connection
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static Connection getConnection(SysDbConnInfo connectInfo) throws SQLException, ClassNotFoundException {
		Class.forName(connectInfo.getDriver());
		if(connectInfo.getDriver().indexOf("sqlite")>-1){
			return DriverManager.getConnection(connectInfo.getUrl());
		}
		return DriverManager.getConnection(connectInfo.getUrl(),connectInfo.getUsername(),connectInfo.getPassword());
	}


	/**
	 * 获取connection
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static PreparedStatement getPreparedStatement(Connection connection,String sql) throws SQLException, ClassNotFoundException {
	    PreparedStatement statement = connection.prepareStatement(sql);
	    return statement;
	}

	/**
	 * 默认查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public static List<Map<String,Object>> queryForList(String sql,String ... params){
		return queryForList(defaultConnInfo,sql,params);
	}

	/**
	 * 默认查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public static Map<String,Object> queryForMap(String sql,String ... params){
		return queryForMap(defaultConnInfo,sql,params);
	}

	/**
	 * 通用sql 执行  create  delete  update truncate drop 不支持query
	 * @param sql
	 * @param params
	 * @return
	 */
	public static boolean execute(String sql,String ... params){
		return execute(defaultConnInfo,sql,params);
	}

	/**
	 * 通用sql 执行  create  delete  update truncate drop 不支持query
	 * @param connInfo
	 * @param sql
	 * @param params
	 * @return
	 */
	public static boolean execute(SysDbConnInfo connInfo,String sql,String ... params){
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = DeanJdbcUtils.getConnection(connInfo);
			statement = connection.prepareStatement(sql);
			// 处理参数
			for (int i = 0; i < params.length; i++) {
				String param = params[i];
				statement.setString(i+1, param);
			}
			statement.execute();
		} catch (SQLException e) {
			logger.debug("执行sql出现异常:{}"+e.getMessage(),e);
		} catch (ClassNotFoundException e) {
			logger.debug("执行sql出现异常:{}"+e.getMessage(),e);
		}finally {
			DeanJdbcUtils.close(statement,connection);
		}
		return true;
	}

	public static boolean batchInsert(SysDbConnInfo connInfo,String sql,List<List<String>> lists) {
		return batchInsert(connInfo,sql,lists,null);
	}

		/**
         * 批量插入功能 remark为空时 则不插入该字段  该字段主要时文件名称
         * @param connInfo
         * @param sql
         * @param lists
         * @param remark
         * @return
         */
	public static boolean batchInsert(SysDbConnInfo connInfo,String sql,List<List<String>> lists,String remark) {
		logger.info("即将执行如下sql:{}",sql);
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection(connInfo);
			ps = conn.prepareStatement(sql);
			// 关闭自动提交
			conn.setAutoCommit(false);
			int count=lists.size();
			int i;
			for (i = 0;i < count ; i++) {
				List<String> record = lists.get(i);
				// logger.debug("{}",record);
				for (int j = 0; j < record.size(); j++) {
					ps.setString(j+1,record.get(j));
				}
				if(StrUtil.isNotBlank(remark)){
					// 处理文件备注字段 文件名+sheetname
					ps.setString(record.size()+1, remark);
				}
				ps.addBatch();
			}
			logger.debug("正在插入第"+(i+1)+"条数据");
			ps.executeBatch();
			conn.commit();
			ps.clearBatch();
		} catch (Exception e) {
			// 发生异常时回滚数据
			try {
				conn.rollback();
			} catch (SQLException ex) {
				logger.debug("数据批量插入出现异常:{}"+e.getMessage(),e);
				return false;
			}
			logger.debug("数据批量插入出现异常:{}"+e.getMessage(),e);
			return false;
		} finally {
			DeanJdbcUtils.close(ps, conn);
		}
		logger.debug("数据批量插入完成,完成时间:{}", LocalDateTime.now());
		return true;
	}

	/**
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
    public static TableInfo queryTableInfo(SysDbConnInfo connInfo,String sql, String ... params){
		TableInfo tableInfo = new TableInfo();
		Connection conn = null;
    	PreparedStatement statement = null;
		ResultSet resultSet = null ;
    	try{
			conn = DeanJdbcUtils.getConnection(connInfo);
			statement =  conn.prepareStatement(sql);
			// 设置传入参数
			for (int i = 0; i < params.length; i++) {
				String param = params[i];
				statement.setString(i+1, param);
			}
			// 执行查询
			resultSet = statement.executeQuery();
			// 获取表头信息
			ResultSetMetaData resMetaData = resultSet.getMetaData();
			// 存储表头
			int columnCount = resMetaData.getColumnCount();
			// 读取表头
			List<String> fieldNames = new ArrayList<String>(columnCount);
			for(int i = 1;i<=columnCount; i++){
				fieldNames.add(resMetaData.getColumnLabel(i));
			}
			// 只取一行数据
			if (resultSet.next()){
				for (String fieldName : fieldNames) {
					tableInfo.set(fieldName,resultSet.getString(fieldName));
				}
				tableInfo.setEntityName(StrUtil.toCamelCase(tableInfo.getTableName()));

				tableInfo.setFirstUpperEntityName(StrUtil.upperFirst(tableInfo.getEntityName()));
			}
		}catch (Exception e){
			logger.error(e.getMessage(),e);
			DeanRuntimeException.throwException(2002,"执行数据脚本异常,请检查日志");
		}finally {
			DeanJdbcUtils.close(resultSet,statement,conn);
		}
		return tableInfo;
	}


	/**
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public static List<TableField> queryTableFields(SysDbConnInfo connInfo,String sql, String ... params){
		logger.info("执行sql:[{}]{}",sql,params);
		List<TableField> fields = new ArrayList<TableField>();
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null ;
		try{
			conn = DeanJdbcUtils.getConnection(connInfo);
			statement =  conn.prepareStatement(sql);
			// 设置传入参数
			for (int i = 0; i < params.length; i++) {
				String param = params[i];
				statement.setString(i+1, param);
			}
			resultSet = statement.executeQuery();
			// 获取表头信息
			ResultSetMetaData resMetaData = resultSet.getMetaData();
			// 存储表头
			int columnCount = resMetaData.getColumnCount();
			// 读取表头
			List<String> fieldNames = new ArrayList<String>(columnCount);
			for(int i = 1;i<=columnCount; i++){
				fieldNames.add(resMetaData.getColumnLabel(i));
			}
			// 只取一行数据
			while (resultSet.next()){
				TableField tableField = new TableField();
				for (String fieldName : fieldNames) {
					tableField.set(fieldName,resultSet.getString(fieldName));
				}
				//
				// String fieldName = StrUtil.toCamelCase(tableField.getColumnName().toLowerCase());
				// tableField.setFieldName(fieldName);
				// tableField.setFirstUpperFieldName(StrUtil.upperFirst(fieldName));
				String fieldType = DeanEntityUtils.coverDataType(tableField.getDataType(),tableField.getDataLength(),tableField.getDataScale());
				tableField.setFieldType(fieldType);
				fields.add(tableField);
			}
		}catch (Exception e){
			logger.error(e.getMessage(),e);
			DeanRuntimeException.throwException(2002,"执行数据脚本异常,请检查日志");
		}finally {
			DeanJdbcUtils.close(resultSet,statement,conn);
		}
		return fields;
	}

	/**
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public static Map<String,Object> queryForMap(SysDbConnInfo connInfo,String sql,String ... params){
		Map<String,Object> result = new HashMap<String,Object>(16);
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null ;
		try{
			conn = DeanJdbcUtils.getConnection(connInfo);
			statement =  conn.prepareStatement(sql);
			// 设置传入参数
			for (int i = 0; i < params.length; i++) {
				String param = params[i];
				statement.setString(i+1, param);
			}
			resultSet = statement.executeQuery();
			// 获取表头信息
			ResultSetMetaData resMetaData = resultSet.getMetaData();
			// 存储表头
			int columnCount = resMetaData.getColumnCount();
			// 读取表头
			List<String> fieldNames = new ArrayList<String>(columnCount);
			for(int i = 1;i<=columnCount; i++){
				fieldNames.add(resMetaData.getColumnLabel(i));
			}
			// 只取一行数据
			if (resultSet.next()){
				for (String fieldName : fieldNames) {
					result.put(fieldName,resultSet.getString(fieldName));
				}
			}
		}catch (Exception e){
			logger.error(e.getMessage(),e);
		}finally {
			DeanJdbcUtils.close(resultSet,statement,conn);
		}
		return result;
	}


	/**
	 *
	 * @param sql
	 * @param params
	 * @return
	 */
	public static List<Map<String,Object>> queryForList(SysDbConnInfo connInfo,String sql,String ... params){
		List<Map<String,Object>> results = new ArrayList<Map<String,Object>>();
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null ;
		try{
			conn = DeanJdbcUtils.getConnection(connInfo);
			statement =  conn.prepareStatement(sql);
			// 设置传入参数
			for (int i = 0; i < params.length; i++) {
				String param = params[i];
				statement.setString(i+1, param);
			}
			resultSet = statement.executeQuery();
			// 获取表头信息
			ResultSetMetaData resMetaData = resultSet.getMetaData();
			// 存储表头
			int columnCount = resMetaData.getColumnCount();
			// 读取表头
			List<String> fieldNames = new ArrayList<String>(columnCount);
			for(int i = 1;i<=columnCount; i++){
				fieldNames.add(resMetaData.getColumnName(i));
			}
			// 只取一行数据
			while (resultSet.next()){
				Map<String,Object> result = new HashMap<String,Object>(16);
				for (String fieldName : fieldNames) {
					result.put(fieldName,resultSet.getString(fieldName));
				}
				results.add(result);
			}
		}catch (Exception e){
			logger.error(e.getMessage(),e);
		}finally {
			DeanJdbcUtils.close(resultSet,statement,conn);
		}
		return results;
	}

	public List<T> queryForList(String sql,Class<T> clazz,String ... params){
		return queryForList(defaultConnInfo,sql,clazz,params);
	}

	public List<T> queryForList(SysDbConnInfo connInfo,String sql,Class<T> clazz,String ... params){
		List<Map<String,Object>> list = queryForList(connInfo,sql,params);
		if(list!=null && list.size()>0){
			List<T> results = new ArrayList<T>(list.size());
			try {
				for (Map<String, Object> map : list) {
					T t = clazz.newInstance();
					Method method = clazz.getMethod("set",Map.class);
					method.invoke(t,map);
					results.add(t);
				}
			} catch (InstantiationException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			}
			return results;
		}
		return null;

	}

	public T queryForObject(String sql,Class<T> clazz,String ... params){
		return queryForObject(defaultConnInfo,sql,clazz,params);
	}
	public T queryForObject(SysDbConnInfo connInfo,String sql,Class<T> clazz,String ... params){
		T t = null;
		Map<String,Object> map = queryForMap(connInfo,sql,params);
		if(!map.isEmpty()){
			try {
				t = clazz.newInstance();
				Method method = clazz.getMethod("set",Map.class);
				method.invoke(t,map);
			} catch (InstantiationException e) {
				logger.error("实例化错误:{},错误原因:{}",clazz.getName(),e.getMessage(),e);
			} catch (IllegalAccessException e) {
				logger.error("实例化错误:{},错误原因:{}",clazz.getName(),e.getMessage(),e);
			} catch (NoSuchMethodException e) {
				logger.error("{}类没有set(Map)方法，不能注入",clazz.getName());
			} catch (InvocationTargetException e) {
				logger.error("{}类没有执行set(Map)方法失败：{}",clazz.getName(),e.getMessage(),e);
			}
		}
		return t;
	}

	/**
	 *  easyexcel 导出文件 根据sql查询结果
	 *
	 * @param sql
	 * @param params 查询参数
	 * @return
	 */
	public static ExcelQueryResult query(SysDbConnInfo connectInfo,String sql,String ... params){
		List<List<String>> values = new ArrayList<List<String>>();
		List<List<String>> heads = new ArrayList<List<String>>();
		ExcelQueryResult excelQueryResult = new ExcelQueryResult(heads,values);

		Connection conn = null ;
		PreparedStatement statement = null ;
		ResultSet resultSet = null ;
		try {
			conn = DeanJdbcUtils.getConnection(connectInfo);
			statement =  conn.prepareStatement(sql);
			// 设置传入参数
			for (int i = 0; i < params.length; i++) {
				String param = params[i];
				statement.setString(i+1, param);
			}
			resultSet = statement.executeQuery();
			// 获取表头信息
			ResultSetMetaData resMetaData = resultSet.getMetaData();
			// 存储表头
			int columnCount = resMetaData.getColumnCount();
			// 读取表头
			for(int i = 1;i<=columnCount; i++){
				List<String> header = new ArrayList<String>();
				header.add(resMetaData.getColumnName(i));
				heads.add(header);
			}
			List<String> tableInfo;
		    while(resultSet.next()){
		    	tableInfo = new ArrayList<String>();
				for(int i = 1;i<=columnCount; i++){
					tableInfo.add(resultSet.getString(resMetaData.getColumnName(i)));
				}
				values.add(tableInfo);
		    }
		} catch (Exception e) {
			logger.error(e.getMessage(),e);
		}finally {
			DeanJdbcUtils.close(resultSet,statement,conn);
		}
		return excelQueryResult;
	}

	public static DataQueryEntity queryDataAndHead(String sql, String ... params){
		logger.debug("数据查询开始,即将执行查询sql[{}]",sql);
		DataQueryEntity dataQueryEntity = new DataQueryEntity();
		Connection conn = null ;
		PreparedStatement statement = null ;
		ResultSet resultSet = null ;
		try {
			conn = DeanJdbcUtils.getConnection(defaultConnInfo);
			statement =  conn.prepareStatement(sql);
			// 设置传入参数
			for (int i = 0; i < params.length; i++) {
				String param = params[i];
				statement.setString(i+1, param);
			}
			resultSet = statement.executeQuery();
			// 获取表头信息
			ResultSetMetaData resMetaData = resultSet.getMetaData();
			// 存储表头
			int columnCount = resMetaData.getColumnCount();
			// 读取表头
			String [] header = new String[columnCount];

			for(int i = 0;i < columnCount; i++){
				header[i] = resMetaData.getColumnName(i+1);
			}
			List<String []> rows = new ArrayList<String[]>();

			while(resultSet.next()){
				String [] data =  new String[columnCount];
				for(int i = 0; i < columnCount; i++){
					data[i] = resultSet.getString(i+1);
				}
				rows.add(data);
			}
			dataQueryEntity.setCode(0);
			dataQueryEntity.setMsg("QUERY SUCCESS DATA SIZE IS "+rows.size());
			dataQueryEntity.setHeader(header);
			String [][] datas = new String[rows.size()][columnCount];
			dataQueryEntity.setDatas(rows.toArray(datas));
		} catch (Exception e) {
			dataQueryEntity.setCode(-1);
			dataQueryEntity.setMsg("数据查询出错,错误原因:"+e.getMessage());
			logger.debug("数据查询出错,错误原因:{}",e.getMessage(),e);
		}finally {
			DeanJdbcUtils.close(resultSet,statement,conn);
		}
		logger.debug("数据查询结束");
		return dataQueryEntity;
	}

	/**
	 * 前台查询功能不分页
	 * @param connInfo
	 * @param sql
	 * @param params
	 * @return
	 */
	public static DataQueryEntity queryDataAndHead(SysDbConnInfo connInfo,String sql, String ... params){
		return queryDataAndHead(connInfo,sql,null,params);
	}

	/**
	 * 前台查询功能
	 * @param sql
	 * @param params
	 * @return
	 */
	public static DataQueryEntity queryDataAndHead(SysDbConnInfo connInfo,String sql,Integer limit, String ... params){
		logger.debug("数据查询开始,即将执行查询sql[{}]",sql);
		DataQueryEntity dataQueryEntity = new DataQueryEntity();
		Connection conn = null ;
		PreparedStatement statement = null ;
		ResultSet resultSet = null ;
		try {
			conn = DeanJdbcUtils.getConnection(connInfo);
			statement =  conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			// 设置传入参数
			for (int i = 0; i < params.length; i++) {
				String param = params[i];
				statement.setString(i+1, param);
			}
			if(limit !=null ){
				statement.setInt(params.length+1,limit);
			}
			resultSet = statement.executeQuery();
			// 获取表头信息
			ResultSetMetaData resMetaData = resultSet.getMetaData();
			// 存储表头
			int columnCount = resMetaData.getColumnCount();
			// 读取表头
			String [] header = new String[columnCount];

			for(int i = 0;i < columnCount; i++){
				header[i] = resMetaData.getColumnName(i+1);
			}
			resultSet.last();
			int rownums = resultSet.getRow();
			logger.debug("当前共查询到{}行数据",rownums);
			resultSet.beforeFirst();
			String [][] datas = new String[rownums][columnCount];
			String [] tableInfo;
			int j = 0;
			while(resultSet.next()){
				for(int i = 0; i < columnCount; i++){
					datas[j][i] = resultSet.getString(i+1);
				}
				j++;
			}
			dataQueryEntity.setCode(0);
			dataQueryEntity.setMsg("QUERY SUCCESS DATA SIZE IS "+rownums);
			dataQueryEntity.setHeader(header);
			dataQueryEntity.setDatas(datas);
		} catch (Exception e) {
			dataQueryEntity.setCode(-1);
			dataQueryEntity.setMsg("数据查询出错,错误原因:"+e.getMessage());
			logger.debug("数据查询出错,错误原因:{}",e.getMessage(),e);
		}finally {
			DeanJdbcUtils.close(resultSet,statement,conn);
		}
		logger.debug("数据查询结束");
		return dataQueryEntity;
	}
    /**
     * 释放资源
     * @param statement
     * @param connection
     */
    public static void close(PreparedStatement statement,Connection connection){
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
               logger.error(throwables.getMessage(),throwables);
            }
        }
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
				logger.error(throwables.getMessage(),throwables);
            }
        }
    }

    /**
     * 释放资源
     * @param res
     * @param statement
     * @param connection
     */
    public static void close(ResultSet res,PreparedStatement statement, Connection connection){
        if(res != null){
            try {
                res.close();
            } catch (SQLException throwables) {
				logger.error(throwables.getMessage(),throwables);
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
				logger.error(throwables.getMessage(),throwables);
            }
        }
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
				logger.error(throwables.getMessage(),throwables);
            }
        }
    }
}
